﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Popw.LiveBlog.Config;
using Popw.LiveBlog.Models;
using Popw.LiveBlog.Repositories.Cache;

namespace Popw.LiveBlog.Repositories
{
	public interface IContentItemRepo
	{
		ContentItem Get(int contentItemID);
		List<ContentItem> GetTopContentItems(int count);
		List<ContentItem> GetTopContentItemsAfterTime(int count, DateTime time);
		int GetOldestID();
		void SaveContentItem(ContentItem contentItem);
		void Delete(int contentItemID);
	}

	public class ContentItemRepo : IContentItemRepo
	{
		private readonly ICacheProvider _cacheProvider;

		public ContentItemRepo(ICacheProvider cacheProvider)
		{
			_cacheProvider = cacheProvider;
		}

		private static class CacheKeys
		{
			public const string OldestID = "LiveBlog.OldestID";
			public const string Newest = "LiveBlog.Newest";
			public const string Block = "LiveBlog.Block.";
		}

		public ContentItem Get(int contentItemID)
		{
			ContentItem item = null;
			using (var connection = new SqlConnection(ConnectionStrings.AzureDatabaseString))
			{
				var sql = String.Format("SELECT ContentItemID, Name, Content, PostTime FROM ContentItems WHERE ContentItemID = @ContentItemID");
				var command = new SqlCommand(sql, connection);
				command.Parameters.AddWithValue("@ContentItemID", contentItemID);
				connection.Open();
				var reader = command.ExecuteReader();
				if (reader.Read())
					item = PopulateContentItem(reader);
				connection.Close();
			}
			return item;
		}

		public List<ContentItem> GetTopContentItems(int count)
		{
			var key = CacheKeys.Newest;
			var cachedObject = _cacheProvider.Get<List<ContentItem>>(key);
			if (cachedObject != null)
				return cachedObject;
			var sql = String.Format("SELECT TOP {0} ContentItemID, Name, Content, PostTime FROM ContentItems ORDER BY PostTime DESC", count);
			var list = GetContentItems(sql);
			_cacheProvider.Add(key, list, 60000);
			return list;
		}

		public List<ContentItem> GetTopContentItemsAfterTime(int count, DateTime time)
		{
			var key = CacheKeys.Block + time.Ticks;
			var cachedObject = _cacheProvider.Get<List<ContentItem>>(key);
			if (cachedObject != null)
				return cachedObject;
			var sql = String.Format("SELECT TOP {0} ContentItemID, Name, Content, PostTime FROM ContentItems WHERE PostTime < '{1}' ORDER BY PostTime DESC", count, time.ToString("G"));
			var list = GetContentItems(sql);
			_cacheProvider.Add(key, list, 60000);
			return list;
		}

		public int GetOldestID()
		{
			var key = CacheKeys.OldestID;
			var cachedObject = _cacheProvider.GetValue<int>(key);
			if (cachedObject.HasValue)
				return cachedObject.Value;
			int oldest;
			using (var connection = new SqlConnection(ConnectionStrings.AzureDatabaseString))
			{
				var sql = String.Format("SELECT TOP 1 ContentItemID FROM ContentItems ORDER BY PostTime");
				var command = new SqlCommand(sql, connection);
				connection.Open();
				oldest = Convert.ToInt32(command.ExecuteScalar());
				connection.Close();
			}
			_cacheProvider.Add(key, oldest, 60000);
			return oldest;
		}

		private static List<ContentItem> GetContentItems(string sql)
		{
			var list = new List<ContentItem>();
			using (var connection = new SqlConnection(ConnectionStrings.AzureDatabaseString))
			{
				var command = new SqlCommand(sql, connection);
				connection.Open();
				var reader = command.ExecuteReader();
				while (reader.Read())
				{
					list.Add(PopulateContentItem(reader));
				}
				reader.Close();
				connection.Close();
			}
			return list;
		}

		private static ContentItem PopulateContentItem(SqlDataReader reader)
		{
			return new ContentItem
			{
				ContentItemID = reader.GetInt32(0),
				Name = reader.GetString(1),
				Content = reader.GetString(2),
				PostTime = reader.GetDateTime(3)
			};
		}

		public void SaveContentItem(ContentItem contentItem)
		{
			_cacheProvider.Remove(CacheKeys.Newest);
			using (var connection = new SqlConnection(ConnectionStrings.AzureDatabaseString))
			{
				var sql = String.Format("INSERT INTO ContentItems (Name, Content, PostTime) VALUES (@Name, @Content, @PostTime)");
				var command = new SqlCommand(sql, connection);
				command.Parameters.AddWithValue("@Name", contentItem.Name);
				command.Parameters.AddWithValue("@Content", contentItem.Content);
				command.Parameters.AddWithValue("@PostTime", contentItem.PostTime);
				connection.Open();
				command.ExecuteNonQuery();
				command.Parameters.Clear();
				command.CommandText = "SELECT @@IDENTITY";
				contentItem.ContentItemID = Convert.ToInt32(command.ExecuteScalar());
				connection.Close();
			}
		}

		public void Delete(int contentItemID)
		{
			_cacheProvider.Remove(CacheKeys.Newest);
			_cacheProvider.Remove(CacheKeys.OldestID);
			using (var connection = new SqlConnection(ConnectionStrings.AzureDatabaseString))
			{
				var sql = String.Format("DELETE FROM ContentItems WHERE ContentItemID = @ContentItemID");
				var command = new SqlCommand(sql, connection);
				command.Parameters.AddWithValue("@ContentItemID", contentItemID);
				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();
			}
		}
	}
}